package com.bs.areport.service.util;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.net.aso.i;

import org.springframework.ui.context.Theme;

import com.bs.areport.sys.*;
import com.bs.restframework.context.util.SystemContextUtil;
import com.bs.restframework.db.Database;
import com.bs.restframework.session.UserSession;
import com.bs.restframework.util.GUID;

/**
 * 
 * <p>函数名称：通用数据服务类</p>
 * <p>创建时间：2012-03-07</p>
 * <p>作者：李新宇</p>
 */
public class DataUtil {
	
	/**
	 * 
	 * <p>函数名称：getLevel</p>
	 * <p>功能说明：获取级数</p>
	 * <p>参数说明：jc:级长  ; code: 编码</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static Integer getLevel(String jc,String code){
		Integer level=null;
		Integer len=0;
		
		 for(int i=0;i<jc.length();i++){
	        	len=len+Integer.parseInt(jc.substring(i, i+1));
	        	if(len==code.length())
	        		level=i+1;
	        }
		
		return level;
	}
	
	/**
	 * 
	 * <p>函数名称：getListByPage</p>
	 * <p>功能说明：查询列表</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static Map<String, Object> getListByPage(Database db,Map<String, Object> valueMap,char type,boolean filteDept,boolean isLeftFuzzy,boolean isRightFuzzy,String vcondition) throws Exception
	{
		Map<String, Object> resultMap=new HashMap<String, Object>();
		String deptSql="";
		String deptConditon="";
		
		//获取用户单位信息
		if(filteDept){
			String token =(String)valueMap.get("Token");
			UserSession userSession = SystemContextUtil.getSessionMange().getUserSession(token);
			String deptCode=(String)userSession.getAttribute("deptCode");
			if (deptCode!=null&&!"".equals(deptCode)){
				//在查询单位信息时不休要左联
				if(type==EntityType.ARP_BSC_DEPT)
					deptConditon=" AND M.F_CODE LIKE '"+deptCode+"%' ";
				else{	
					deptSql=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=M.F_DEPTID";
					deptConditon=" AND D.F_CODE LIKE '"+deptCode+"%' ";
				}
			}
		}
		
		int page = Integer.valueOf((String)valueMap.get("page"));
		int rows = Integer.valueOf((String)valueMap.get("rows"));
		List<Object> params = new ArrayList<Object>();
		String sql ="SELECT  M.*  FROM "+ EntityType.getTableName(type)+" M ";
		sql+=deptSql;
		sql+=" WHERE 1=1 ";
		sql+=deptConditon;
		String conditon="";
		
		 for(String k : valueMap.keySet()){
		    	if(EntityType.getTypeMap(type).get(k)!=null
		    			&&!"".equals(EntityType.getTypeMap(type).get(k))
		    			&&valueMap.get(k)!=null
		    			&&!"F_ID".equals(k.toUpperCase())&&!"".equals(valueMap.get(k)))
		    	{
		    		conditon += " AND M." + k.toUpperCase() ;
		    		if(isLeftFuzzy||isRightFuzzy){
		    			conditon +=" LIKE ? ";
		    			if(isLeftFuzzy)
		    			    valueMap.put(k, "%"+valueMap.get(k));
		    			if(isRightFuzzy)
		    				valueMap.put(k,valueMap.get(k)+"%");
		    		}else
		    			conditon +="=?";
		    		
		    		params.add(valueMap.get(k));
		    	}
		    }
		 conditon+= vcondition;
		sql+=conditon;  
		sql+=" ORDER BY M.F_CODE";
		vcondition="";
		 
		sql = Tools.formatPagingSQL(sql,rows,page);
		
		List<Map> listMap = db.queryList(Map.class, sql,params.size() == 0 ? null : params.toArray());
		
		int count = getUserListCount(db,params,type,conditon,filteDept,deptSql,deptConditon);
		


				
		resultMap.put("rows", listMap);
		resultMap.put("total", count);
		
		params=null;
		return resultMap;
	}
	
	/**
	 * 
	 * <p>函数名称：getUserGroupListByPage</p>
	 * <p>功能说明：用户组列表查询</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型;filteDept: 是否过滤单位;isLeftFuzzy:是否左模糊查询; isRightFuzzy:是否右模糊查询</p>
	 * <p>创建时间：2012-04-12</p>
	 * <p>作者：陈金滨</p>
	 */
	public static Map<String, Object> getUserGroupListByPage(Database db,Map<String, Object> valueMap,char type,boolean filteDept,boolean isLeftFuzzy,boolean isRightFuzzy) throws Exception
	{
		Map<String, Object> resultMap=new HashMap<String, Object>();
		String deptSql="";
		String deptConditon="";		

		/*获取登陆用户信息:单位编码,单位ID*/
		String token =(String)valueMap.get("Token");
		UserSession userSession = SystemContextUtil.getSessionMange().getUserSession(token);
		String deptCode=(String)userSession.getAttribute("deptCode");
		String deptid = (String)userSession.getAttribute("deptID");
		String ID = (String) valueMap.get("F_ID");

		/*按单位ID过滤*/
		if (deptid!=null&&!"".equals(deptid))
		{	
			deptConditon+=" AND M.F_DEPTID = '"+deptid+"' ";
		}
		/*用户组ID过滤*/
		if(ID!=null&&"".equals(ID)==false)
		{
			deptConditon+=" AND M.F_ID='"+ID+"'";
		}

		
		int page = Integer.valueOf((String)valueMap.get("page"));
		int rows = Integer.valueOf((String)valueMap.get("rows"));
		
		List<Object> params = new ArrayList<Object>();
		String sql ="SELECT  M.*  FROM "+ EntityType.getTableName(type)+" M ";
		sql+=deptSql;
		sql+=" WHERE 1=1 ";
		sql+=deptConditon;
		String conditon="";
		
		 for(String k : valueMap.keySet()){
		    	if(EntityType.getTypeMap(type).get(k)!=null
		    			&&!"".equals(EntityType.getTypeMap(type).get(k))
		    			&&valueMap.get(k)!=null
		    			&&!"F_ID".equals(k.toUpperCase())&&!"".equals(valueMap.get(k)))
		    	{
		    		conditon += " AND M." + k.toUpperCase() ;
		    		if(isLeftFuzzy||isRightFuzzy){
		    			conditon +=" LIKE ? ";
		    			if(isLeftFuzzy)
		    			    valueMap.put(k, "%"+valueMap.get(k));
		    			if(isRightFuzzy)
		    				valueMap.put(k,valueMap.get(k)+"%");
		    		}else
		    			conditon +="=?";
		    		
		    		params.add(valueMap.get(k));
		    	}
		    }
		
		sql+=conditon;  
		sql+=" ORDER BY to_number(M.F_CODE)";
		 
		sql = Tools.formatPagingSQL(sql,rows,page);
		
		List<Map> listMap = db.queryList(Map.class, sql,params.size() == 0 ? null : params.toArray());
		
		int count = getUserListCount(db,params,type,conditon,filteDept,deptSql,deptConditon);	
				
		resultMap.put("rows", listMap);
		resultMap.put("total", count);
		
		params=null;
		return resultMap;
	}	
	/**
	 * 
	 * <p>函数名称：getListe</p>
	 * <p>功能说明：查询列表</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static List<Map> getComboboxList(Database db,Map<String, Object> valueMap,char type,boolean filteDept,boolean isLeftFuzzy,boolean isRightFuzzy,String vcondition) throws Exception
	{		
		List<Object> params = new ArrayList<Object>();
		
		String deptSql="";
		String deptConditon="";
		
		//获取用户单位信息
		if(filteDept){
			String token =(String)valueMap.get("Token");
			UserSession userSession = SystemContextUtil.getSessionMange().getUserSession(token);
			String deptCode=(String)userSession.getAttribute("deptCode");
			if (deptCode!=null&&!"".equals(deptCode)){
				//在查询单位信息时不休要左联
				if(type==EntityType.ARP_BSC_DEPT)
					deptConditon=" AND M.F_CODE LIKE '"+deptCode+"%' ";
				else{
					if(type!=EntityType.SYS_RIGHT){
					deptSql=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=M.F_DEPTID";
					deptConditon=" AND D.F_CODE LIKE '"+deptCode+"%' ";
					}
				}
			}
		}
		
		
		String sql =" SELECT M.F_ID AS \"id\", '[' || M.F_CODE || ']' || M.F_NAME AS \"text\" FROM "+EntityType.getTableName(type)+" M";
		sql+=deptSql;		
		sql+=" WHERE 1=1 ";
		sql+=deptConditon;
		sql+=vcondition ;
		String conditon="";
		
		 for(String k : valueMap.keySet()){
		    	if(EntityType.getTypeMap(type).get(k)!=null
		    			&&!"".equals(EntityType.getTypeMap(type).get(k))
		    			&&valueMap.get(k)!=null
		    			&&!"F_ID".equals(k.toUpperCase())&&!"".equals(valueMap.get(k)))
		    	{
		    		conditon += " AND M." + k.toUpperCase() ;
		    		if(isLeftFuzzy){
		    			conditon +=" LIKE ? ";
		    			valueMap.put(k, "%"+valueMap.get(k));
		    			if(isRightFuzzy)
		    				valueMap.put(k,valueMap.get(k)+"%");	
		    		}else
		    			conditon +="=?";
		    		
		    		params.add(valueMap.get(k));
		    	}
		    }
		
		sql+=conditon;  
		
		sql+=" ORDER BY to_number(M.F_CODE)";
		
		List<Map> listMap = db.queryList(Map.class, sql,params.size() == 0 ? null : params.toArray());
		
		params=null;
		
		return listMap;
	}
	
	/**
	 * 
	 * <p>函数名称：getList</p>
	 * <p>功能说明：查询列表</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static List<Map> getList(Database db,Map<String, Object> valueMap,char type,boolean filteDept,boolean isLeftFuzzy,boolean isRightFuzzy) throws Exception
	{			
		List<Object> params = new ArrayList<Object>();
		
		String deptSql="";
		String deptConditon="";
		
		//获取用户单位信息
		if(filteDept){
			String token =(String)valueMap.get("Token");
			UserSession userSession = SystemContextUtil.getSessionMange().getUserSession(token);
			String deptCode=(String)userSession.getAttribute("deptCode");
			if (deptCode!=null&&!"".equals(deptCode)){
				//在查询单位信息时不休要左联
				if(type==EntityType.ARP_BSC_DEPT)
					deptConditon=" AND M.F_CODE LIKE '"+deptCode+"%' ";
				else{
					if(type!=EntityType.SYS_USERRIGHT){
					deptSql=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=M.F_DEPTID";
					deptConditon=" AND D.F_CODE LIKE '"+deptCode+"%' ";
					}
				}
			}
		}
		
		String sql =" SELECT M.* FROM "+EntityType.getTableName(type)+" M";
		sql+=deptSql;
		sql+=" WHERE 1=1 ";
		sql+=deptConditon;
		String conditon="";
		
		 for(String k : valueMap.keySet()){
		    	if(EntityType.getTypeMap(type).get(k)!=null
		    			&&!"".equals(EntityType.getTypeMap(type).get(k))
		    			&&valueMap.get(k)!=null
		    			&&!"F_ID".equals(k.toUpperCase())&&!"".equals(valueMap.get(k)))
		    	{
		    		conditon += " AND M." + k.toUpperCase() ;
		    		if(isLeftFuzzy){
		    			conditon +=" LIKE ? ";
		    			valueMap.put(k, "%"+valueMap.get(k));
		    			if(isRightFuzzy)
		    				valueMap.put(k,valueMap.get(k)+"%");	
		    		}else
		    			conditon +="=?";
		    		
		    		params.add(valueMap.get(k));
		    	}
		    }
		
		sql+=conditon;  
		
		List<Map> listMap = db.queryList(Map.class, sql,params.size() == 0 ? null : params.toArray());
		
		params=null;
		
		return listMap;
	}
	
	/**
	 * 
	 * <p>函数名称：getListForTree</p>
	 * <p>功能说明：查询列表生成树需要的结果</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static List<Map> getTree(Database db,Map<String, Object> valueMap,char type,boolean filteDept,String condition) throws Exception
	{
	
		String sql="";
		String id = (String)valueMap.get("id");
		
		String deptSql="";
		String deptConditon="";
		
		//获取用户单位信息
		if(filteDept){
			String token =(String)valueMap.get("Token");
			UserSession userSession = SystemContextUtil.getSessionMange().getUserSession(token);
			String deptCode=(String)userSession.getAttribute("deptCode");
			if (deptCode!=null&&!"".equals(deptCode)){
				//在查询单位信息时不休要左联
				String dwjc= MethodUtil.getDeptJC(db);
				if(type==EntityType.ARP_BSC_DEPT){
					deptConditon=" AND M.F_CODE LIKE '"+deptCode+"%' ";
				if(id==null||"".equals(id))
					deptConditon+=" AND M.F_LEVEL="+getLevel(dwjc, deptCode).toString();
				}
				else{
					if(type!=EntityType.SYS_RIGHT){
					deptSql=" LEFT JOIN "+EntityType.ARP_BSC_DEPT_TABLE+" D ON D.F_ID=M.F_DEPTID";
					deptConditon=" AND D.F_CODE LIKE '"+deptCode+"%' ";
					}
				}
			}
		}
		
		List<Map> resultList=null;
		
		sql =" SELECT M.F_ID AS \"id\", '[' || M.F_CODE || ']' || M.F_NAME AS \"text\" ,";
		sql += " CASE M.F_ISLEAF WHEN 0 THEN 'closed' ELSE '' END AS \"state\" FROM "+EntityType.getTableName(type)+" M";
		sql+=deptSql;
		sql+=" WHERE 1=1 ";
		sql+=deptConditon;
		if (condition!=null&&!"".equals(condition)){
			sql += condition;
		}
		if (id == "" || id == null){
			if("".equals(deptConditon))
			    sql += " AND M.F_LEVEL<=1 " ;
		}
		else
		{
			
			String sqlcode="SELECT F_CODE,F_LEVEL FROM "+EntityType.getTableName(type)+" WHERE F_ID='"+id+"'";
			Map fthrNode=db.queryObject(Map.class, sqlcode, null);
			Integer level=(Integer.parseInt(fthrNode.get("F_LEVEL").toString())+1);
			sql += " AND M.F_LEVEL="+level.toString()+" AND M.F_CODE LIKE '" + fthrNode.get("F_CODE") + "%' " ;
			if (condition!=null&&!"".equals(condition)){
				sql += condition;
			}
		}
		sql+=" ORDER BY to_number(M.F_CODE)" ;
		List<Map> listMap = db.queryList(Map.class, sql,null); 
		
		if(listMap!=null&&"".equals(listMap)==false)
		{	
			if((id==null||"".equals(id))&&("".equals(deptConditon))){
				Map<String, Object> root=new HashMap<String, Object>();
				root.put("id", "所有");
				root.put("text", "所有");
				root.put("children", listMap);
				resultList=new ArrayList<Map>();
				resultList.add(root);
				
			}else {
				resultList=listMap;
			}
		}
		
		return resultList;
	}
	
	
	/**
	 * 
	 * <p>函数名称：getUserListCount</p>
	 * <p>功能说明：获取表记录数</p>
	 * <p>参数说明：db:数据源 ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */
	public static int getUserListCount(Database db,List<Object>  params,char type,String condition,boolean filteDept,String deptSql,String deptConditon)throws Exception {

		String sql = "SELECT Count(1) FROM "+EntityType.getTableName(type)+" M";
		sql+=deptSql;
		sql+=" WHERE 1=1 ";
		sql+=deptConditon;
		sql+=condition;
		int Count =db.queryObject(BigDecimal.class, sql , params.size() == 0 ? null : params.toArray()).intValue();

		return Count;
	}
	

	/**
	 * 
	 * <p>函数名称：add</p>
	 * <p>功能说明：添加</p>
	 * <p>参数说明：db:数据源  ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */	
	
	public static void add(Database db,Map<String, Object> valueMap,char type) throws Exception {
		
		StringBuffer sqlBng=new StringBuffer();
		StringBuffer sqlEnd=new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		
		sqlBng.append("INSERT INTO ");
		sqlBng.append(EntityType.getTableName(type));
		sqlBng.append(" (F_ID,");
		sqlEnd.append("VALUES(?,");
		params.add(GUID.newGUID());
		
		for(String k : valueMap.keySet()){
	    	if(EntityType.getTypeMap(type).get(k)!=null
	    			&&!"".equals(EntityType.getTypeMap(type).get(k))
	    			&&valueMap.get(k)!=null
	    			&&!"F_ID".equals(k.toUpperCase())&&!"".equals(valueMap.get(k))){
	    		sqlBng.append(k.toUpperCase()+",");
	    		sqlEnd.append("?,");
	    		params.add(valueMap.get(k));
	    	}
	    	
	    }
		
		sqlBng.deleteCharAt(sqlBng.lastIndexOf(","));
		sqlEnd.deleteCharAt(sqlEnd.lastIndexOf(","));
		
		sqlBng.append(")");
		sqlEnd.append(")");
		
		
		sqlBng.append(sqlEnd.toString());
		
		db.update(sqlBng.toString(), params.size() == 0 ? null : params.toArray());  

		sqlBng=null;
		sqlEnd=null;
		params=null;
		
	}

	/**
	 * 
	 * <p>函数名称：update</p>
	 * <p>功能说明：修改</p>
	 * <p>参数说明：db:数据源  ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */	
	
	public static void update(Database db,Map<String, Object> valueMap,char type) throws Exception {
		
		StringBuffer sql=new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		
		sql.append("UPDATE ");
		sql.append(EntityType.getTableName(type));
		sql.append(" SET ");
		for(String k : valueMap.keySet()){
	    	if(EntityType.getTypeMap(type).get(k)!=null
	    			&&!"".equals(EntityType.getTypeMap(type).get(k))
	    			&&valueMap.get(k)!=null){
	    		if (!"F_ID".equals(k.toUpperCase())){
	    		    sql.append(k.toUpperCase()+"=?,"); 
	    		    params.add(valueMap.get(k));
	    		}
	    	}
	    	
	    }
		
		sql.deleteCharAt(sql.lastIndexOf(","));
		
		sql.append(" WHERE F_ID=?");
		params.add(valueMap.get("F_ID"));
		db.update(sql.toString(), params.size() == 0 ? null : params.toArray());
		
		
		sql=null;
		params=null;
	}
	
	/**
	 * 
	 * <p>函数名称：del</p>
	 * <p>功能说明：删除</p>
	 * <p>参数说明：db:数据源  ; valueMap:值列表  ; type: 数据对象类型</p>
	 * <p>创建时间：2012-03-07</p>
	 * <p>作者：李新宇</p>
	 */	
	public static void del(Database db,Map<String, Object> valueMap,char type,boolean isLeftFuzzy,boolean isRightFuzzy) throws Exception {
		
		StringBuffer sql=new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		
		sql.append("DELETE FROM ");
		sql.append(EntityType.getTableName(type));
		sql.append(" WHERE 1=1 ");
		String conditon="";
		
		 for(String k : valueMap.keySet()){
		    	if(EntityType.getTypeMap(type).get(k)!=null
		    			&&!"".equals(EntityType.getTypeMap(type).get(k))
		    			&&valueMap.get(k)!=null
		    			&&!"".equals(valueMap.get(k)))
		    	{
		    		conditon += " AND " + k.toUpperCase();
		    		if(isLeftFuzzy){
		    			conditon +=" LIKE ? ";
		    			valueMap.put(k, "%"+valueMap.get(k));
		    			if(isRightFuzzy)
		    				valueMap.put(k,valueMap.get(k)+"%");	
		    		}else
		    			conditon +="=?";
		    		
		    		params.add(valueMap.get(k));
		    	}
		    }
		
		sql.append(conditon);
		
		db.update(sql.toString(), params.size() == 0 ? null : params.toArray());
		
		
		sql=null;
		params=null;
		
	}
}
